﻿using CCWin;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TelephoneYiShou
{
    public partial class FrmSalesMGT : Skin_Mac
    {
        public DataTable dt;
        int i, start;//a表示总行数，start表示当前位置
        int size = 6;//size表示一页显示的数量
        string str1 = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID";
        string str = "server=.;database = TelephoneYiShou;uid = sa;pwd = 123456";
        public FrmSalesMGT()
        {
            InitializeComponent();
        }

        //显示数据
        private void FrmSalesMGT_Load(object sender, EventArgs e)
        {
            //查询订单表
            string ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID";
            dt = DBHelper.GetDataTable(ssql);
            i = dt.Rows.Count;//获取数据的条数
            show(0, size);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            //查询数据库中操作员的名字
            string sssql = "select * from employee";
            DataTable dts = DBHelper.GetDataTable(sssql);
            for (int i = 0; i < dts.Rows.Count; i++)
            {
                txtempid.Items.Add(dts.Rows[i][1]);
            }
            //商品名称绑定
            string sqlname = "select distinct commodityname from commodity";
            DataTable dte = DBHelper.GetDataTable(sqlname);
            for (int i = 0; i < dte.Rows.Count; i++)
            {
                txtgoodsname.Items.Add(dte.Rows[i][0]);
            }
            //顾客姓名绑定
            string sqlclname = "select distinct clientname from client";
            DataTable cldt = DBHelper.GetDataTable(sqlclname);
            for (int i = 0; i < cldt.Rows.Count; i++)
            {
                txtsellname.Items.Add(cldt.Rows[i][0]);
            }

        }
        private void txtcolor_MouseEnter(object sender, EventArgs e)
        {
            //颜色数据绑定
            txtcolor.Items.Clear();
            string sqlcolor = string.Format("select colour from commodity where commodityname = '{0}'", txtgoodsname.Text);
            DataTable dtes = DBHelper.GetDataTable(sqlcolor);
            for (int i = 0; i < dtes.Rows.Count; i++)
            {
                txtcolor.Items.Add(dtes.Rows[i][0]);
            }
        }

        private void btn_update_Click(object sender, EventArgs e)
        {
            if (la.Text == "请选择")
            {
                MessageBoxEx.Show("请先选择修改的订单");
                return;
            }
            string goodsid = null;
            string sellid = null;
            string empid = null;
            //查询商品编号
            if (!string.IsNullOrEmpty(txtgoodsname.Text))
            {
                string sql = string.Format("select commodityid from commodity where commodityname='{0}'", txtgoodsname.Text);
                goodsid = DBHelper.GetDataTable(sql).Rows[0][0].ToString();
            }
            //查询客户编号
            if (!string.IsNullOrEmpty(txtsellname.Text))
            {
                string sql = string.Format("select clientid from client where clientname='{0}'", txtsellname.Text);
                sellid = DBHelper.GetDataTable(sql).Rows[0][0].ToString();
            }
            //查询操作员的d编号
            if (!string.IsNullOrEmpty(txtempid.Text))
            {
                string sql = string.Format("select employeeid from employee where employeename='{0}'", txtempid.Text);
                DataTable dtss = DBHelper.GetDataTable(sql);//执行
                empid = dtss.Rows[0][0].ToString();
            }
            string sum = txtsum.Text;
            string date = txtdate.Text;
            string money = txtmoney.Text;
            string fufei = txtfangshi.Text;
            string beizhu = txtbeizhu.Text;
            if (string.IsNullOrEmpty(date))
            {
                date = DateTime.Now.ToString();
            }
            if (string.IsNullOrEmpty(goodsid) || string.IsNullOrEmpty(sellid) || string.IsNullOrEmpty(empid) || string.IsNullOrEmpty(sum) || string.IsNullOrEmpty(date) || string.IsNullOrEmpty(money) || string.IsNullOrEmpty(fufei) || string.IsNullOrEmpty(beizhu))
            {
                MessageBoxEx.Show("请填写完整");
            }
            else
            {
                string comid = la.Text;
                string usql =string.Format("update sellgoods set commodityid = '{0}', clientid = '{1}',number='{2}',ordertime='{3}',paytype='{4}',moneysum='{5}',remark='{6}',employeeid='{7}' where orderid = '{8}'", goodsid, sellid,sum,date, fufei,money, beizhu,empid,comid);
                if (DBHelper.ExecuteNonQuery(usql))
                {
                    MessageBoxEx.Show("修改成功");
                    start = 0;
                    string ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID ";
                    dt = DBHelper.GetDataTable(ssql);
                    i = dt.Rows.Count;//获取数据的条数
                    show(0, size);
                    labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                }
            }
        }
        //删除
        private void button2_Click(object sender, EventArgs e)
        {
            if (la.Text == "请选择")
            {
                MessageBoxEx.Show("请先选择删除的订单");
                return;
            }
            string comid = la.Text;
            string dsql = string.Format("delete sellgoods where orderid = '{0}'", comid);
            if (DBHelper.ExecuteNonQuery(dsql))
            {
                MessageBoxEx.Show("删除成功");
                start = 0;
                string ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID";
                dt = DBHelper.GetDataTable(ssql);
                i = dt.Rows.Count;//获取数据的条数
                show(0, size);
                labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            }
        }

        private void dataGridView1_Click(object sender, EventArgs e)
        {
            la.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            txtgoodsname.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            txtcolor.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
            txtsellname.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
            txtempid.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
            txtsum.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
            txtdate.Text = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
            txtfangshi.Text = dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
            txtmoney.Text = dataGridView1.SelectedRows[0].Cells[8].Value.ToString();
            txtbeizhu.Text = dataGridView1.SelectedRows[0].Cells[9].Value.ToString();
        }

        private void txtmoney_MouseEnter(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(txtgoodsname.Text) && !string.IsNullOrEmpty(txtsum.Text) && !string.IsNullOrEmpty(txtcolor.Text))
                {
                    string idsql = string.Format("select commodityid from commodity where commodityname = '{0}' and colour = '{1}'", txtgoodsname.Text, txtcolor.Text);
                    string txtgoodsid = DBHelper.GetDataTable(idsql).Rows[0][0].ToString();
                    string ssmsql = string.Format("select sellgoodsprice from commodity where commodityid = '{0}'", txtgoodsid);
                    DataTable sumdt = DBHelper.GetDataTable(ssmsql);
                    double sums = double.Parse(sumdt.Rows[0][0].ToString()) * double.Parse(txtsum.Text);
                    txtmoney.Text = sums.ToString();
                }
                else
                {
                    txtmoney.Text = null;
                }
            }
            catch (Exception)
            {

                MessageBoxEx.Show("请查看你的输入是否错误");
            }
        }
        //下一页
        private void buttnextpage_Click(object sender, EventArgs e)
        {
            if (start + 1 > i / size)
            {
                MessageBoxEx.Show("已是尾页");
                show(start * size, size);
            }
            else
            {
                start = start + 1;
                show(start * size, size);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }

        private void buttpreviouspage_Click(object sender, EventArgs e)
        {
            if (start < 1)
            {
                MessageBoxEx.Show("已是第一页");
                show(0, size);
            }
            else
            {
                start = start - 1;
                show(start * size, size);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据

        }

        //show方法
        public void show(int i, int j)
        {
            SqlConnection sqlcon = new SqlConnection(str);
            sqlcon.Open();
            SqlCommand sqlcmd = new SqlCommand(str1, sqlcon);
            SqlDataAdapter sqlada = new SqlDataAdapter(sqlcmd);
            DataSet ds = new DataSet();
            sqlada.Fill(ds, i, j, "[stock]");//把数据库表中的i-j的记录数用来填充数据集
            dataGridView1.DataSource = ds.Tables["[stock]"];
            ds = null;//清空数据集
        }
    }
}
